"""
告警事件管理相关的数据库操作
"""
from datetime import datetime, timedelta
from typing import List, Optional, Tuple, Dict, Any

from app.models.alarm_models import Alarm, AlarmRule, AlarmDetail, AlarmTimelineItem
from app.schemas.alarm_schemas import (
	AlarmListRequest, AlarmListItem, AlarmDetailResponse,
	AlarmRuleResponse, AlarmTypeItem, RegionAlarmStatsItem, AlarmTypeStatsItem
)
from common.base_enums import AlarmStatus, AlarmCloseReason, AlarmRuleSeverity
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger(__name__)


def safe_slice(value, length):
	return value[:length] if value and len(value) >= length else None


# ---------- 告警基础操作 ----------
async def get_alarm_by_id(db: DatabaseService, alarm_id: int) -> Optional[Alarm]:
	"""根据ID获取告警"""
	sql = """
        SELECT id, meter_code, alarm_type, status, close_reason,
               raised_at, updated_at
        FROM am_alarm
        WHERE id = %s
    """
	row = db.get_one(sql, (alarm_id,))
	return Alarm(**row) if row else None


async def create_alarm(db: DatabaseService, meter_code: str, alarm_type: str) -> int:
	"""创建告警，使用INSERT IGNORE防止重复"""
	sql = """
        INSERT IGNORE INTO am_alarm
        (meter_code, alarm_type, status, raised_at, updated_at)
        VALUES (%s, %s, %s, NOW(), NOW())
    """
	rows = db.insert(sql, (meter_code, alarm_type, AlarmStatus.OPEN.value))
	if rows == 0:
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR)
	return rows


async def update_alarm_status(
		db: DatabaseService,
		alarm_id: int,
		status: AlarmStatus,
		close_reason: Optional[AlarmCloseReason] = None,
		recover_token: Optional[str] = None) -> bool:
	"""更新状态 & token；返回是否更新成功"""
	sql = """
        UPDATE am_alarm
           SET status = %(st)s,
               updated_at = NOW(),
               close_reason = %(cr)s,
               recover_token = %(tk)s
         WHERE id = %(id)s
    """
	rowcount = db.update(sql, dict(
		st=status.value,
		cr=close_reason.value if close_reason else None,
		tk=recover_token,
		id=alarm_id))
	return rowcount > 0


async def insert_ack_log(
		db: DatabaseService,
		alarm_id: int,
		actor: str,
		action: str) -> int:
	sql = """
        INSERT INTO am_alarm_ack_log (alarm_id, actor, action, ack_at)
        VALUES (%s, %s, %s, NOW())
    """
	return db.insert(sql, (alarm_id, actor, action))


async def get_open_alarm_by_meter_and_type(db: DatabaseService, meter_code: str, alarm_type: str) -> Optional[Alarm]:
	"""根据电表编码和告警类型获取开启状态的告警"""
	sql = """
        SELECT id, meter_code, alarm_type, status, close_reason,
               raised_at, updated_at
        FROM am_alarm
        WHERE meter_code = %s AND alarm_type = %s AND status IN (%s, %s)
        ORDER BY raised_at DESC
        LIMIT 1
    """
	row = db.get_one(sql, (meter_code, alarm_type, AlarmStatus.OPEN.value, AlarmStatus.ACKED.value))
	return Alarm(**row) if row else None


# ---------- 告警列表 ----------
async def get_alarm_list(db: DatabaseService, request: AlarmListRequest) -> Tuple[int, List[AlarmListItem]]:
	"""获取告警列表"""
	import time
	start_time = time.time()

	where, params = ["1=1"], []

	if request.status:
		try:
			status_enum = AlarmStatus(request.status)
		except ValueError:
			raise BizException.from_error_code(
				ErrorCode.BAD_REQUEST,
				message=f"无效告警状态: {request.status}"
			)
		where.append("a.status = %s")
		params.append(status_enum.value)
	if request.keyword:
		where.append("a.meter_code LIKE %s")
		params.append(request.keyword)

	if request.alarm_type:
		where.append("a.alarm_type = %s")
		params.append(request.alarm_type)

	if request.station_id:
		where.append("v.station_id = %s")
		params.append(request.station_id)

	region_code = (
			safe_slice(request.area_code, 6) or
			safe_slice(request.city_code, 4) or
			safe_slice(request.province_code, 2)
	)
	if region_code:
		where.append("addr.region_code LIKE %s")
		params.append(f"%{region_code}%")

	# 时间过滤
	if request.start_time:
		where.append("a.raised_at >= %s")
		params.append(request.start_time)
	if request.end_time:
		where.append("a.raised_at <= %s")
		params.append(request.end_time)

	where_clause = " AND ".join(where)

	# 获取总数
	count_sql = f"""
        SELECT COUNT(*) as total
        FROM am_alarm a
        LEFT JOIN dev_device_address addr ON addr.device_code = a.meter_code
        INNER JOIN v_all_device v on v.serial_no = addr.device_code and addr.device_type = v.device_type
        WHERE {where_clause}
    """
	result = db.get_one(count_sql, params)
	total = result["total"] if result else 0

	# 获取列表
	offset = (request.page - 1) * request.size
	list_sql = f"""
        SELECT a.id, a.meter_code, a.alarm_type, r.rule_name, a.status, a.raised_at,
          a.close_reason, base.full_name
        FROM am_alarm a
        LEFT JOIN am_alarm_rule r ON a.alarm_type = r.rule_code
        LEFT JOIN dev_device_address addr ON addr.device_code = a.meter_code
        LEFT JOIN v_all_device v on v.serial_no = addr.device_code and addr.device_type = v.device_type
        LEFT JOIN base_region_address base ON base.id = addr.address_id
        WHERE {where_clause}
        ORDER BY a.raised_at DESC
        LIMIT %s OFFSET %s
    """
	params.extend([request.size, offset])
	rows = db.get_all(list_sql, params)

	items = [AlarmListItem(**row) for row in rows]

	logger.info(f"[性能] 告警列表 - 总耗时: {(time.time() - start_time)*1000:.2f}ms")
	return total, items


# ---------- 告警详情 ----------
async def get_alarm_detail(db: DatabaseService, alarm_id: int) -> Optional[AlarmDetailResponse]:
	"""获取告警详情"""
	# 获取告警基本信息
	alarm = await get_alarm_by_id(db, alarm_id)
	if not alarm:
		return None

	# 获取最近24小时的电表数据作为当前值
	telemetry_sql = """
        SELECT voltage, current
        FROM dc_meter_telemetry
        WHERE meter_code = %s AND ts >= %s
        ORDER BY ts DESC
        LIMIT 1
    """
	twenty_four_hours_ago = datetime.now() - timedelta(hours=24)
	telemetry_row = db.get_one(telemetry_sql, (alarm.meter_code, twenty_four_hours_ago))

	current_voltage = telemetry_row["voltage"] if telemetry_row else None
	current_current = telemetry_row["current"] if telemetry_row else None

	# 构造时间线
	timeline = []

	# 告警产生节点
	timeline.append(AlarmTimelineItem(
		time=alarm.raised_at,
		action="告警产生",
		comment="系统自动检测到异常"
	))

	# 确认节点
	if alarm.status in [AlarmStatus.ACKED, AlarmStatus.CLOSED]:
		# 这里应该从审计日志或其他地方获取确认时间
		# 简化处理，使用更新时间作为确认时间
		timeline.append(AlarmTimelineItem(
			time=alarm.updated_at,
			action="人工确认",
			comment="操作员已知晓"
		))

	# 关闭节点
	if alarm.status == AlarmStatus.CLOSED and alarm.close_reason:
		timeline.append(AlarmTimelineItem(
			time=alarm.updated_at,
			action="告警关闭",
			comment=f"关闭原因: {alarm.close_reason.value}"
		))

	alarm_detail = AlarmDetail(
		id=alarm.id,
		meter_code=alarm.meter_code,
		alarm_type=alarm.alarm_type,
		status=alarm.status,
		close_reason=alarm.close_reason,
		work_order_id=alarm.work_order_id,
		raised_at=alarm.raised_at,
		updated_at=alarm.updated_at,
		current_voltage=current_voltage,
		current_current=current_current,
		timeline=timeline
	)

	return AlarmDetailResponse(**alarm_detail.model_dump())


# ---------- 告警规则 ----------
async def get_alarm_rule_by_code(db: DatabaseService, rule_code: str) -> Optional[AlarmRule]:
	"""根据规则编码获取告警规则"""
	sql = """
        SELECT id, rule_code, severity, enabled
        FROM am_alarm_rule
        WHERE rule_code = %s
    """
	row = db.get_one(sql, (rule_code,))
	return AlarmRule(**row) if row else None


async def get_enabled_alarm_rules(db: DatabaseService) -> List[AlarmRuleResponse]:
	"""获取启用的告警规则"""
	sql = """
        SELECT id, rule_name, rule_code, severity, enabled, threshold,unit, operator
        FROM am_alarm_rule
        WHERE enabled = 1
    """
	rows = db.get_all(sql)

	items: List[AlarmRuleResponse] = []
	for row in rows:
		try:
			severity_label = AlarmRuleSeverity(row["severity"]).label()
		except ValueError:  # 兜底，防止库里有 0/4/NULL 等脏数据
			severity_label = "未知"

		items.append(AlarmRuleResponse(
			id=row["id"],
			rule_name=row["rule_name"],
			rule_code=row["rule_code"],
			severity=row['severity'],
			enabled=bool(row["enabled"]),
			threshold=row["threshold"],
			unit=row["unit"],
			operator=row["operator"],
			severity_label=severity_label
		))
	return items


# ---------- 告警统计接口 ----------
async def get_alarm_types(db: DatabaseService) -> List[AlarmTypeItem]:
	"""获取告警类型列表

	从am_alarm_rule表中获取所有告警类型，按sort_order排序

	Returns:
		List[AlarmTypeItem]: 告警类型列表
	"""
	try:
		sql = """
		SELECT
		  a.alarm_type,
		  r.rule_name AS alarm_name
		FROM
		  am_alarm a
		  INNER JOIN am_alarm_rule r ON r.rule_code = a.alarm_type
		WHERE
		  a.`status` = 'open'
		group by a.alarm_type,r.rule_name
		HAVING count(*) > 5
		ORDER BY r.rule_code desc
		"""

		rows = db.get_all(sql)
		return [AlarmTypeItem(**row) for row in rows]
	except Exception as e:
		logger.error(f"获取告警类型列表失败: {e}")
		return []


async def get_region_alarm_stats(
		db: DatabaseService,
		region_code: Optional[str] = None,
		alarm_type: Optional[str] = None,
		start_time: Optional[datetime] = None,
		end_time: Optional[datetime] = None
) -> List[RegionAlarmStatsItem]:
	"""获取区域告警统计

	根据省市区代码查询下级区域的告警统计数据

	Args:
		db: 数据库服务
		region_code: 区域代码（6位）
		alarm_type: 告警类型（可选）
		start_time: 开始时间
		end_time: 结束时间

	Returns:
		List[RegionAlarmStatsItem]: 区域告警统计列表
	"""
	try:

		# 构建查询条件
		where_conditions = []
		params = []

		# 区域条件
		if region_code:
			where_conditions.append("dev.region_code LIKE %s")
			params.append(f"{region_code}%")

		# 告警类型条件
		if alarm_type:
			where_conditions.append("a.alarm_type = %s")
			params.append(alarm_type)

		# 时间条件
		if start_time:
			where_conditions.append("a.raised_at >= %s")
			params.append(start_time)
		if end_time:
			where_conditions.append("a.raised_at <= %s")
			params.append(end_time)

		where_clause = " AND ".join(where_conditions)

		sql = f"""
				SELECT
					dev.station_id AS region_id,
					GROUP_CONCAT(DISTINCT dev.region_code) AS region_code,
					COUNT(*) AS alarm_count,
					GROUP_CONCAT(DISTINCT s.name) AS region_name
				FROM am_alarm a
				INNER JOIN am_alarm_rule ar on ar.rule_code = a.alarm_type
				LEFT JOIN (
					SELECT
					addr.device_id,
					addr.device_type,
					addr.region_code,
					COALESCE(s.serial_no, b.serial_no, r.serial_no, m.serial_no) AS serial_no,
					COALESCE(s.id, b.station_id, r.station_id, m.station_id) AS station_id
				FROM dev_device_address addr
				LEFT JOIN dev_station s ON s.id = addr.device_id AND addr.device_type = 'STATION'
				LEFT JOIN dev_box b ON b.id = addr.device_id AND addr.device_type = 'BOX'
				LEFT JOIN dev_rtu r ON r.id = addr.device_id AND addr.device_type = 'RTU'
				LEFT JOIN dev_meter m ON m.id = addr.device_id AND addr.device_type = 'METER'
				WHERE addr.deleted = 0 AND addr.is_primary = 1
				) AS dev ON dev.serial_no = a.meter_code
				LEFT JOIN dev_station s on s.id = dev.station_id
				WHERE a.status = 'OPEN' AND station_id > 0 AND
				   {where_clause}
				GROUP BY dev.station_id
			"""

		rows = db.get_all(sql, params)
		return [RegionAlarmStatsItem(**row) for row in rows]
	except Exception as e:
		logger.error(f"获取区域告警统计失败: {e}")
		return []


async def get_alarm_type_stats(
		db: DatabaseService,
		region_code: Optional[str] = None,
		station_id: Optional[int] = None,
		start_time: Optional[datetime] = None,
		end_time: Optional[datetime] = None,
		limit: int = 10
) -> List[AlarmTypeStatsItem]:
	"""获取告警类型统计

	根据区域代码查询各告警类型的数量统计

	Args:
		db: 数据库服务
		region_code: 区域代码
		station_id: 台区ID
		start_time: 开始时间
		end_time: 结束时间
		limit: 返回数量限制

	Returns:
		List[AlarmTypeStatsItem]: 告警类型统计列表
	"""
	try:
		# 构建查询条件
		where_conditions = ["a.status = 'OPEN'"]
		params = []

		# 区域条件
		if region_code:
			where_conditions.append("dev.region_code LIKE %s")
			params.append(f"{region_code}%")

		if station_id:
			where_conditions.append("dev.station_id = %s")
			params.append(station_id)

		# 时间条件
		if start_time:
			where_conditions.append("a.raised_at >= %s")
			params.append(start_time)
		if end_time:
			where_conditions.append("a.raised_at <= %s")
			params.append(end_time)

		where_clause = " AND ".join(where_conditions)

		sql = f"""
		SELECT
			a.alarm_type,
			COUNT(*) AS alarm_count,
			GROUP_CONCAT(DISTINCT ar.rule_name) AS alarm_name,
			GROUP_CONCAT(a.meter_code ORDER BY a.meter_code) AS meter_codes
		FROM am_alarm a
		INNER JOIN am_alarm_rule ar on ar.rule_code = a.alarm_type
		LEFT JOIN (
			SELECT
				CASE addr.device_type
					WHEN 'STATION' THEN st.serial_no
					WHEN 'BOX'     THEN bx.serial_no
					WHEN 'RTU'     THEN rt.serial_no
					WHEN 'METER'   THEN mt.serial_no
				END AS serial_no,
				addr.region_code,
				COALESCE(st.id, bx.station_id, rt.station_id, mt.station_id) AS station_id
			FROM dev_device_address addr
			LEFT JOIN dev_station st ON st.id = addr.device_id AND addr.device_type = 'STATION'
			LEFT JOIN dev_box     bx ON bx.id = addr.device_id AND addr.device_type = 'BOX'
			LEFT JOIN dev_rtu     rt ON rt.id = addr.device_id AND addr.device_type = 'RTU'
			LEFT JOIN dev_meter   mt ON mt.id = addr.device_id AND addr.device_type = 'METER'
			WHERE addr.deleted = 0
			  AND addr.is_primary = 1
		) AS dev ON dev.serial_no = a.meter_code
		WHERE
		 {where_clause}
		GROUP BY a.alarm_type
		LIMIT %s
		"""
		params.append(limit)

		rows = db.get_all(sql, params)
		return [AlarmTypeStatsItem(**row) for row in rows]
	except Exception as e:
		logger.error(f"获取告警类型统计失败: {e}")
		return []


# ---------- 告警工单关联 ----------
async def create_alarm_work(db: DatabaseService, alarm_id: int, work_order_id: int) -> int:
	"""创建告警工单关联"""
	sql = """
        INSERT INTO alarm_work (alarm_id, work_order_id, created_at)
        VALUES (%s, %s, NOW())
    """
	return db.insert(sql, (alarm_id, work_order_id))


# ---------- 告警数据查询 ----------
async def get_alarm_detail_by_id(db: DatabaseService, alarm_id: int) -> Optional[Dict[str, Any]]:
	"""
	根据ID获取告警详细信息

	Args:
		db: 数据库服务
		alarm_id: 告警ID

	Returns:
		Optional[Dict]: 告警详细信息
	"""
	try:
		sql = """
		SELECT
			a.id,
			a.meter_code,
			a.meter_type,
			a.alarm_type,
			a.raised_at,
			ar.severity,
			ar.rule_name,
			COALESCE(s.name, CONCAT('电表-', a.meter_code)) as device_name
		FROM am_alarm a
		LEFT JOIN am_alarm_rule ar ON a.alarm_type = ar.rule_code
		LEFT JOIN dev_meter m ON a.meter_code = m.code
		LEFT JOIN dev_rtu r ON m.rtu_id = r.id
		LEFT JOIN dev_box b ON r.box_id = b.id
		LEFT JOIN dev_station s ON b.station_id = s.id
		WHERE a.id = %s
		"""

		result = db.get_one(sql, (alarm_id,))
		return result

	except Exception as e:
		logger.error(f"根据ID获取告警详情失败: {e}")
		return None


# ---------- 异常设备告警记录 ----------
async def get_abnormal_device_alarms(
		db: DatabaseService,
		device_type: str,
		station_id: Optional[int],
		start_time: datetime,
		end_time: datetime,
		page: int,
		size: int
) -> Tuple[int, int, List[Dict[str, Any]]]:
	"""
	获取异常设备告警记录列表

	Args:
		db: 数据库服务
		device_type: 设备类型 (METER/RTU/BOX)
		station_id: 台区ID
		start_time: 开始时间
		end_time: 结束时间
		page: 页码
		size: 每页大小

	Returns:
		Tuple[int, List[Dict]]: 总条数和告警列表
	"""
	try:
		# 构建查询条件
		where_conditions = ["a.status = 'OPEN'"]
		params = []

		# 时间条件
		if start_time:
			where_conditions.append("a.raised_at >= %s")
			params.append(start_time)
		if end_time:
			where_conditions.append("a.raised_at <= %s")
			params.append(end_time)

		# 设备类型条件
		if device_type:
			where_conditions.append("addr.device_type = %s")
			params.append(device_type)

		# 台区条件
		if station_id:
			where_conditions.append("dev.station_id = %s")
			params.append(station_id)

		where_clause = " AND ".join(where_conditions)

		# 获取总数
		count_sql = f"""
		SELECT
		  COUNT(DISTINCT a.meter_code) as abnormal_total,
		  COUNT(*) as total
		FROM am_alarm a
		INNER JOIN dev_device_address addr ON addr.device_code = a.meter_code
		INNER JOIN (
		  SELECT
			addr.device_id,
			addr.device_type,
			v.serial_no,
			v.station_id,
			v.`status`
		  FROM v_all_device v
		  INNER JOIN  dev_device_address addr on v.device_id = addr.device_id and v.device_type =  addr.device_type
		  WHERE addr.deleted = 0 AND addr.is_primary = 1 AND v.status != 'ONLINE'
		) AS dev ON dev.serial_no = addr.device_code AND dev.device_type = addr.device_type
		WHERE {where_clause}
		"""
		result = db.get_one(count_sql, params)
		total = result["total"] if result else 0
		abnormal_total = result["abnormal_total"] if result else 0

		# 获取列表
		offset = (page - 1) * size
		list_sql = f"""
		SELECT
			a.meter_code as serial_no,
			a.alarm_type,
			a.raised_at,
			w.code as work_order_code,
			w.id as work_order_id,
			ba.id as address_id,
			ba.name as address_name,
			ba.full_name as full_address,
			ba.longitude,
			ba.latitude,
			addr.device_id,
			addr.address_type,
			addr.is_primary
		FROM am_alarm a
		INNER JOIN dev_device_address addr ON addr.device_code = a.meter_code
		LEFT JOIN base_region_address ba ON addr.address_id = ba.id AND ba.is_active = 1
		INNER JOIN (
			SELECT
				addr.device_id,
				addr.device_type,
				v.serial_no,
				v.station_id,
				v.`status`
			FROM v_all_device v
			INNER JOIN dev_device_address addr on v.device_id = addr.device_id and v.device_type = addr.device_type
			WHERE addr.deleted = 0 AND addr.is_primary = 1 AND v.status != 'ONLINE'
		) AS dev ON dev.serial_no = addr.device_code AND dev.device_type = addr.device_type
		LEFT JOIN am_alarm_work aw ON aw.alarm_id = a.id
		LEFT JOIN wo_work_order w ON w.id = aw.work_order_id AND w.deleted = 0
		WHERE {where_clause}
		ORDER BY a.raised_at DESC
		LIMIT %s OFFSET %s
		"""
		params.extend([size, offset])
		rows = db.get_all(list_sql, params)

		return total, abnormal_total, rows
	except Exception as e:
		logger.error(f"获取异常设备告警记录失败: {e}")
		return 0, 0, []
